IF EXISTS (SELECT * FROM sysobjects WHERE name = 'trUpdCfgCtrl' AND type = 'TR')
     DROP TRIGGER dbo.trUpdCfgCtrl
GO

CREATE TRIGGER dbo.trUpdCfgCtrl ON dbo.CfgCtrl FOR UPDATE
/*
 *
 * trUpdCfgCtrl
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Note(s):      
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 06/11/2009, 1.00.0000
 *
 */
AS
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     -- Handle Last Mod Date Logic
     UPDATE [dbo].[CfgCtrl] 
     SET    [LastModDt] = CURRENT_TIMESTAMP 
     FROM   [dbo].[CfgCtrl] d, 
            inserted i 
     WHERE  i.[CfgCtrlKey] = d.[CfgCtrlKey]

     -- Get changed columns
     DECLARE @CU varbinary(255)
     SET @CU = COLUMNS_UPDATED()

     -- Declare table variable to hold flags indicating that that field changed
     -- in at least one record, we use this to cut the number of fields that the
     -- trigger must actually evaluate to determine whether there was a change.
     DECLARE @TABLE TABLE ( [CfgCtrlKey] tinyint NULL,
                            [CfgCtrlValue] tinyint NULL,
                            [CfgCtrlDescription] tinyint NULL,
                            [LastModDt] tinyint NULL )

     -- Populate the table variable with flags indicating all fields that have 
     -- Changed in any record, this handles the single or multi row update case
     INSERT @TABLE
          ( [CfgCtrlKey],
            [CfgCtrlValue],
            [CfgCtrlDescription],
            [LastModDt] )
     SELECT CASE WHEN SUBSTRING( @CU, 1, 1 ) & POWER(2, 0) > 0 THEN 1 ELSE 0 END,
            CASE WHEN SUBSTRING( @CU, 1, 1 ) & POWER(2, 1) > 0 THEN 1 ELSE 0 END,
            CASE WHEN SUBSTRING( @CU, 1, 1 ) & POWER(2, 2) > 0 THEN 1 ELSE 0 END,
            CASE WHEN SUBSTRING( @CU, 1, 1 ) & POWER(2, 3) > 0 THEN 1 ELSE 0 END

     -- Populate log table, handles changed rows without key changes (normal case)
     INSERT [dbo].[EventLog]
          ( [EventLogTypeId],
            [LogByApp],
            [LogByAppSubSystem],
            [LogByDesc],
            [LogText],
            [MiscVC1],
            [TraceText] )
     SELECT 1, -- Info
            'trUpdCfgCtrl', -- Trigger name
            '', 
            'CfgCtrl', -- Table name
            'Record Modification',
            'Non-Key Change',
            'XML' = ''
                  + '<RecordChange '
                  -- Key Field(s)
                  + 'CfgCtrlKey="'
                  + REPLACE( CONVERT( varchar(8000), i.[CfgCtrlKey] ), '"', '''' )
                  + '" '
                  -- Non-Key Field(s)
                  + CASE WHEN t.[CfgCtrlValue] = 1 
                          AND ( i.[CfgCtrlValue] != d.[CfgCtrlValue]
                             OR ( i.[CfgCtrlValue] IS     NULL AND d.[CfgCtrlValue] IS NOT NULL )
                             OR ( i.[CfgCtrlValue] IS NOT NULL AND d.[CfgCtrlValue] IS     NULL )
                              )
                         THEN 'Pre_CfgCtrlValue="' + ISNULL( REPLACE( CONVERT( varchar(8000), d.[CfgCtrlValue] ), '"', '''' ), '[NULL]' )
                            + '" Post_CfgCtrlValue="' + ISNULL( REPLACE( CONVERT( varchar(8000), i.[CfgCtrlValue] ), '"', '''' ), '[NULL]' )
                            + '" '
                         ELSE ''
                    END
                  + CASE WHEN t.[CfgCtrlDescription] = 1 
                          AND i.[CfgCtrlDescription] != d.[CfgCtrlDescription]
                         THEN 'Pre_CfgCtrlDescription="' + REPLACE( CONVERT( varchar(8000), d.[CfgCtrlDescription] ), '"', '''' )
                            + '" Post_CfgCtrlDescription="' + REPLACE( CONVERT( varchar(8000), i.[CfgCtrlDescription] ), '"', '''' )
                            + '" '
                         ELSE ''
                    END
                  + CASE WHEN t.[LastModDt] = 1 
                          AND i.[LastModDt] != d.[LastModDt]
                         THEN 'Pre_LastModDt="' + REPLACE( CONVERT( varchar(8000), d.[LastModDt] ), '"', '''' )
                            + '" Post_LastModDt="' + REPLACE( CONVERT( varchar(8000), i.[LastModDt] ), '"', '''' )
                            + '" '
                         ELSE ''
                    END
                  + '/>'
     FROM   inserted i, 
            deleted  d,
            @TABLE   t -- Pseudo Cartesian table only has 1 row
     WHERE  i.[CfgCtrlKey] = d.[CfgCtrlKey]

     -- Handle inserted side of key change
     INSERT [dbo].[EventLog]
          ( [EventLogTypeId],
            [LogByApp],
            [LogByAppSubSystem],
            [LogByDesc],
            [LogText],
            [MiscVC1],
            [MiscVC2],
            [TraceText] )
     SELECT 1, -- Info
            'trUpdCfgCtrl', -- Trigger name
            '', 
            'CfgCtrl', -- Table name
            'Record Modification',
            'Key Change',
            'Post Row',
            'XML' = ''
                  + '<RecordChange '
                  + 'CfgCtrlKey="'
                  + REPLACE( CONVERT( varchar(8000), i.[CfgCtrlKey] ), '"', '''' )
                  + '" '
                  + 'CfgCtrlValue="'
                  + ISNULL( REPLACE( CONVERT( varchar(8000), i.[CfgCtrlValue] ), '"', '''' ), '[NULL]' )
                  + '" '
                  + 'CfgCtrlDescription="'
                  + REPLACE( CONVERT( varchar(8000), i.[CfgCtrlDescription] ), '"', '''' )
                  + '" '
                  + 'LastModDt="'
                  + REPLACE( CONVERT( varchar(8000), i.[LastModDt] ), '"', '''' )
                  + '" '
                  + '/>'
     FROM   inserted i
     WHERE  NOT EXISTS ( SELECT 'X'
                         FROM   deleted d
                         WHERE  i.[CfgCtrlKey] = d.[CfgCtrlKey] )

     -- Handle delete side of key change
     INSERT [dbo].[EventLog]
          ( [EventLogTypeId],
            [LogByApp],
            [LogByAppSubSystem],
            [LogByDesc],
            [LogText],
            [MiscVC1],
            [MiscVC2],
            [TraceText] )
     SELECT 1, -- Info
            'trUpdCfgCtrl', -- Trigger name
            '', 
            'CfgCtrl', -- Table name
            'Record Modification',
            'Key Change',
            'Pre Row',
            'XML' = ''
                  + '<RecordChange '
                  + 'CfgCtrlKey="'
                  + REPLACE( CONVERT( varchar(8000), d.[CfgCtrlKey] ), '"', '''' )
                  + '" '
                  + 'CfgCtrlValue="'
                  + ISNULL( REPLACE( CONVERT( varchar(8000), d.[CfgCtrlValue] ), '"', '''' ), '[NULL]' )
                  + '" '
                  + 'CfgCtrlDescription="'
                  + REPLACE( CONVERT( varchar(8000), d.[CfgCtrlDescription] ), '"', '''' )
                  + '" '
                  + 'LastModDt="'
                  + REPLACE( CONVERT( varchar(8000), d.[LastModDt] ), '"', '''' )
                  + '" '
                  + '/>'
     FROM   deleted d
     WHERE  NOT EXISTS ( SELECT 'X'
                         FROM   inserted i
                         WHERE  i.[CfgCtrlKey] = d.[CfgCtrlKey] )
END
GO
